package com.xhh.nphm.system.mapper;

import com.xhh.nphm.common.core.domain.QueryConditionVo;
import com.xhh.nphm.system.domain.MailExcel;
import com.xhh.nphm.system.domain.MmailWorkForm;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Lang;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.harmony.toddler.mybatis.groovy.GroovyLangDriver;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 信件工单Dao
* @author Administrator
* @description 针对表【mMailWorkForm】的数据库操作Mapper
* @createDate 2022-06-07 15:41:25
* @Entity com.xhh.nphm.system.domain.MmailWorkForm
*/
@Mapper
public interface MmailWorkFormDao extends BaseMapper<MmailWorkForm> {
    /**
     * 查询待派-信件工单列表数据
     *
     * @param mmailWorkForm 查询条件
     * @return 列表数据
     */
    @Select("select PkId as myPkVal,SpareString,FlagString,WorkFormNO,SetProID,FromTopic,FromName,FromTel,DCntAName," +
            "dbo.frMDealDeptS(1,PkId,1) as fkDept,OpenX,convert(varchar(10),FromTime,20) as FromTime,convert(varchar(10),WfLimit,20) as WfLimit," +
            "CrtStepName,FmSpareB as FromAccessory,PkId as myOP,Mark from mMailWorkForm where CrtStepID in (100,103) and FKInt != 1 " +
            "and (spareString!='省12345' or spareString is null)  and (FmCerNO is null or FmCerNO = '') and IsDel=0 " +
            "[[and WorkFormNO = #{workFormNo}]] [[and DCntAName =#{dcntAName}]] " +
            "[[and FromName =#{fromName}]] [[and FromTel =#{fromTel}]]")
    List<Map<String, Object>> wfDraftListJson(MmailWorkForm mmailWorkForm);//TODO 缺少根据承办单位查询

    @Select("SELECT COUNT(pkId) FROM  mMailWorkForm  WHERE YEAR(iptTime)=#{fpYear}  AND MONTH(iptTime)=#{fpMth}  AND DAY(iptTime)=#{fpDay}  AND LEN(workFormNo)>0")
    String selectByDate(@Param("fpYear")String fpYear,@Param("fpMth") String fpMth,@Param("fpDay") String fpDay);

    /**
     * 待回访信件工单列表数据
     * @param userNo
     * @param mmailWorkForm
     * @return
     */
    @Select("select m.PkId,m.SpareString,m.FlagString,m.WorkFormNO,m.OriginFK,m.SetProID,m.FromTopic,m.FmAreaName,m.FromName,m.FromTel,m.DFmClaName,convert(varchar(10),WfLimit,20) as lmtDate,m.OpenFrom,convert(varchar(10),DOverTime,20) as overDate from mMailWorkForm m inner join mMailWorkForm_temp mt on m.pkId=mt.wfId  where  m.IsRecord=0 and IsDel=0 and mt.userNo=#{userNo}  [[and m.WorkFormNO=#{mail.workFormNo}]] [[and m.FmAreaName=#{mail.fmAreaName}]] [[and m.FromName=#{mail.fromName}]]")
    List<MmailWorkForm> backWaitListJson(@Param("userNo") String userNo,@Param("mail") MmailWorkForm mmailWorkForm);

    /**
     * 随机获取n条待回访信件工单
     * @param num
     * @return
     */
    @Select("SELECT top (#{num}) mt.wfId,m.PkId,m.SpareString,m.FlagString,m.WorkFormNO,m.OriginFK,m.SetProName,m.FromTopic,m.FmAreaName,m.FromName,m.FromTel,m.DFmClaName,convert(varchar(10),WfLimit,20) as lmtDate,m.OpenFrom,convert(varchar(10),DOverTime,20) as overDate FROM mMailWorkForm m left join mMailWorkForm_temp mt  on m.PkId=mt.wfId where m.IsRecord=0 and IsDel=0   and  mt.wfId is  NULL  order by iptTime desc")
    List<MmailWorkForm> getBackWaitList(Integer num);

    /**
     * 未获取信件工单总数
     * @return
     */
    @Select("SELECT count(0)  FROM mMailWorkForm m left join mMailWorkForm_temp mt  on m.PkId=mt.wfId where m.IsRecord=0 and IsDel=0   and  mt.wfId is  NULL")
    Integer countBackWaitList();

    /**
     * 已回访信件工单列表数据
     * @param mmailWorkForm
     * @return
     */
    @Select("select PkId,SpareString,FlagString,WorkFormNO,OriginFK,SetProName,FromTopic,FmAreaName,FromName,FromTel,DFmClaName,OpenFrom,convert(varchar(10),DOverTime,20) as overDate,VisitFlag from mMailWorkForm where CrtStepID >900 and VisitFlag in (20,21,22,31) and IsDel=0 [[and WorkFormNO=#{workFormNo}]] [[and FmAreaName=#{fmAreaName}]] [[and FromName=#{fromName}]]")
    List<MmailWorkForm> backOverListJson(MmailWorkForm mmailWorkForm);

    /**
     * 待派-信件工单（信件受理目录下）列表数据
     *
     * @param deptId 部门编号
     * @param userNo 用户编号
     * @param mmailWorkForm 查询条件
     * @return 列表数据
     */
    @Select("select PkId as myPkVal,SpareString,FlagString,WorkFormNO,SetProID,FromTopic,FromName,FromTel,DCntAName," +
            "dbo.frMDealDeptS(1,PkId,1) as fkDept,OpenX,convert(varchar(10),FromTime,20) as FromTime,convert(varchar(10),WfLimit,20) as WfLimit," +
            "CrtStepName,FmSpareB as FromAccessory,PkId as myOP,Mark from mMailWorkForm where CrtStepID in (100,103) and FKInt != 1 " +
            "and IsDel=0 and IptWorkNo = #{userNo} and FmCerNO = #{deptId}" +
            "[[and WorkFormNO = #{ety.workFormNo}]] [[and DCntAName =#{ety.dcntAName}]] " +
            "[[and FromName =#{ety.fromName}]] [[and FromTel =#{ety.fromTel}]]")
    List<Map<String, Object>> wfDraftListWllzJson(@Param("deptId") String deptId,@Param("userNo")String userNo,@Param("ety") MmailWorkForm mmailWorkForm);

    /**
     * 信件工单未回复列表数据
     * @param params 查询条件
     * @return 列表数据
     */
    @Select("select PkId as myPkVal,dbo.mWfNOBackDesc(PkId,#{deptId}) cbackDesc,SpareString,DFmClaNo,DFmClaName,FlagString,OriginFK,FmAreaNo,WorkFormNO,SetProID,FromTopic,FromName,FromTel," +
            "DCntACode,DCntAName,dbo.frMDealDeptS(1,PkId,2) as fkDept,OpenX,convert(varchar(10),FromTime,20) as FromTime," +
            "convert(varchar(10),WfLimit,20) as WfLimit,CrtStepName,FmSpareB as FromAccessory" +
            ",PkId as opScan,WsCode,FmCerNO,SBackState from mMailWorkForm where FmCerNO=#{deptId} and SBackState=0 and CrtStepID>399 " +
            "and IsDel=0 [[and WsCode = #{mail.wsCode}]] [[and WorkFormNO = #{mail.workFormNo}]] [[and SetProName = #{mail.setProName}]] " +
            "[[and FromTelA = #{mail.fromTel}]] [[and FromName = #{mail.fromName}]] [[and FmAreaName = #{mail.fmAreaName}]] " +
            "[[and IptWorker = #{mail.iptWorker}]] [[and DFmClaName = #{mail.dfmClaName}]] [[and SpareString = #{mail.spareString}]]")
    List<Map> mailWFNotReplyAllWllzJson(HashMap<String, Object> params);

    //查询下级回复
    @Select("select subBackState cbackDesc from mWfSendBack where SBDeptID=#{DeptId} and PkId=#{PkId} and SendState in(21,23) and BackState in (0,12,22) and IsDel=0")
    String cbackDesc(@Param("PkId")Integer pkId, @Param("DeptId") String deptId);

    /**
     * 信件工单已回复列表数据
     * @param params 查询条件
     * @return 列表数据
     */
    @Select("select PkId as myPkVal,SpareString,FlagString,DFmClaName,OriginFK,WorkFormNO,SetProID,FromTopic,FromName,FromTel,DCntAName," +
            "dbo.frMDealDeptS(1,PkId,2) as fkDept,OpenX,convert(varchar(10),FromTime,20) as FromTime," +
            "convert(varchar(10),WfLimit,20) as WfLimit,CrtStepName,FmSpareB as FromAccessory,PkId as opScan," +
            "WsCode,FmCerNO,SBackState from mMailWorkForm where FmCerNO=#{deptId} and SBackState=10 and CrtStepID>399 and IsDel=0 " +
            "[[and WsCode = #{mail.wsCode}]] [[and WorkFormNO = #{mail.workFormNo}]] [[and SetProName = #{mail.setProName}]] " +
            "[[and FromTelA = #{mail.fromTel}]] [[and FromName = #{mail.fromName}]] [[and FmAreaName = #{mail.fmAreaName}]] " +
            "[[and IptWorker = #{mail.iptWorker}]] [[and DFmClaName = #{mail.dfmClaName}]] [[and SpareString = #{mail.spareString}]]")
    List<Map> mailWFRepliedAllWllzJson(HashMap<String, Object> params);

    /**
     * 查询已删除信件工单 查看页面
     * @param pkId
     * @param map
     * @return
     */
    @Select("select * from mMailWorkForm where PkId = #{pkId}")
    MmailWorkForm getByPkId(Integer pkId);

    /**
     * 根据工单号查询信件工单
     * @param workNo
     * @return
     */
    @Select("select * from mMailWorkForm where WorkFormNO = #{workNo}")
    List<MmailWorkForm> getByWorkFormNO(String workNo);


    /**
     * (网络理政区县）查询本级网络理政件
     * @param sbDeptId 本级部门
     * @param queryConditionVo 参数
     * @return
     */
    @Select("SELECT m1.PkId,m1.WfId,m2.FmContent,m1.MailPK,m2.WorkFormNO,m2.FromTopic,m2.FromName,m2.FromTel," +
            " m3.SpareString,m3.FmAddress,m3.FromTime,m3.DCntAName,m3.DFmClaName,SBDeptName,BackDesc," +
            " SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit," +
            " BackTime,BackState,BackRemark " +
            " FROM mWfSendBack m1 " +
            " left join mMailWorkForm m2 on m1.WfId=m2.PkId AND m2.IsDel=0 " +
            " left join mMailGetDeal m3 on m2.OriginFK=m3.PkId AND m3.IsDel=0 " +
            " WHERE m3.FmCerNO=#{sbDeptId} AND SendState IN(21,22,23,40) AND m1.IsDel=0 " +
            "[[and m2.WorkFormNO like #{mail.workFormNO}+'%']] [[and m3.WsCode like #{mail.wsCode}+'%']]" +
            "[[and m3.SpareString =#{mail.spareString}]]"+
            "[[and convert(varchar(10),m2.IptTime,120) &gt;= #{mail.beginTime}]] [[and convert(varchar(10),m2.IptTime,120) &lt;= #{mail.endTime}]] " +
            "[[and m2.DCntACode like #{mail.dcntAcode}+'%']] " +
            "[[and m2.FromTopic =#{mail.fromTopic}]] [[and m1.sdeptName =#{mail.sdeptName}]]")
    List<Map<String,Object>> netMailWFList(@Param("sbDeptId") String sbDeptId, @Param("mail") QueryConditionVo queryConditionVo);

    @Select("SELECT count(0) " +
            " FROM mWfSendBack m1 " +
            " left join mMailWorkForm m2 on m1.WfId=m2.PkId AND m2.IsDel=0 " +
            " left join mMailGetDeal m3 on m2.OriginFK=m3.PkId AND m3.IsDel=0 " +
            " WHERE m3.FmCerNO=#{sbDeptId} AND SendState IN(21,22,23,40) AND m1.IsDel=0 " +
            "[[and m2.WorkFormNO like #{mail.workFormNO}+'%']] [[and m3.WsCode like #{mail.wsCode}+'%']]" +
            "[[and m3.SpareString =#{mail.spareString}]]"+
            "[[and convert(varchar(10),m2.IptTime,120) &gt;= #{mail.beginTime}]] [[and convert(varchar(10),m2.IptTime,120) &lt;= #{mail.endTime}]] " +
            "[[and m2.DCntACode like #{mail.dcntAcode}+'%']] " +
            "[[and m2.FromTopic =#{mail.fromTopic}]] [[and m1.sdeptName =#{mail.sdeptName}]]")
    long netMailWFList_COUNT(@Param("sbDeptId") String sbDeptId, @Param("mail") QueryConditionVo queryConditionVo);
}




